using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VWPZUORD
    {
        public DataRow Convert2WPZUORD(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryWPZUORD(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["WPZMANDANT"] = " ".PadRight(2 ); 
         dwReturn["WPZKUNDNR"] = " ".PadRight(8 ); 
         dwReturn["WPZWERK"] = " ".PadRight(3 ); 
         dwReturn["WPZABT"] = " ".PadRight(3 ); 
         dwReturn["WPZART"] = " ".PadRight(20 ); 
         dwReturn["WPZVAR"] = " ".PadRight(3 ); 
         dwReturn["WPZDESS"] = " ".PadRight(10 ); 
         dwReturn["WPZBESCHICH"] = " ".PadRight(10 ); 
         dwReturn["WPZZUSTAND"] = " ".PadRight(4 ); 
         dwReturn["WPZLIEFKD"] = " ".PadRight(8 ); 
         dwReturn["WPZLIEFNR"] = " ".PadRight(8 ); 
         dwReturn["WPZPNRKR"] = " ".PadRight(2 ); 
         dwReturn["WPZPLFDNR"] = " ".PadRight(6 ); 
         dwReturn["WPZAENDZL"] = " ".PadRight(0 ); 
         dwReturn["WPZAENDBEN"] = " ".PadRight(8 ); 
         dwReturn["WPZAENDDAT"] = " ".PadRight(0 ); 
         dwReturn["WPZNEUBEN"] = " ".PadRight(8 ); 
         dwReturn["WPZNEUDAT"] = " ".PadRight(0 ); 
         dwReturn["WPZSTUFE"] = " ".PadRight(1 ); 
         dwReturn["WPZGDATINV"] = " ".PadRight(0 ); 
         dwReturn["WPZGDATVON"] = " ".PadRight(0 ); 
         dwReturn["WPZGDATBIS"] = " ".PadRight(0 ); 
         dwReturn["WPZDATINT"] = " ".PadRight(0 ); 
         dwReturn["WPZWEITERE"] = " ".PadRight(1 ); 
         dwReturn["WPZGRUPPE1"] = " ".PadRight(1 ); 
         dwReturn["WPZGRUPPE2"] = " ".PadRight(1 ); 
         dwReturn["WPZGRUPPE3"] = " ".PadRight(1 ); 
         dwReturn["WPZGRUPPE4"] = " ".PadRight(1 ); 
         dwReturn["WPZGRUPPE5"] = " ".PadRight(1 ); 
         dwReturn["WPZGRUPPE6"] = " ".PadRight(1 ); 
         dwReturn["WPZGRUPPE7"] = " ".PadRight(1 ); 
         dwReturn["WPZGRUPPE8"] = " ".PadRight(1 ); 
         dwReturn["WPZGRUPPE9"] = " ".PadRight(1 ); 
         dwReturn["WPZGRUPPE10"] = " ".PadRight(1 ); 
         dwReturn["WPZRPREIS1"] = " ".PadRight(0 ); 
         dwReturn["WPZRPREIS2"] = " ".PadRight(0 ); 
         dwReturn["WPZRPREIS3"] = " ".PadRight(0 ); 
         dwReturn["WPZRPREIS4"] = " ".PadRight(0 ); 
         dwReturn["WPZRPREIS5"] = " ".PadRight(0 ); 
         dwReturn["WPZRPREIS6"] = " ".PadRight(0 ); 
         dwReturn["WPZRPREIS7"] = " ".PadRight(0 ); 
         dwReturn["WPZRPREIS8"] = " ".PadRight(0 ); 
         dwReturn["WPZRPREIS9"] = " ".PadRight(0 ); 
         dwReturn["WPZRPREIS10"] = " ".PadRight(0 ); 
         dwReturn["WPZPRPR"] = " ".PadRight(1 ); 
         dwReturn["WPZTEILQUAL"] = " ".PadRight(1 ); 
         dwReturn["WPZFILLER"] = " ".PadRight(17 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapWPZUORD(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveWPZUORD(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryWPZUORD(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapWPZUORD(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertWPZUORD(dwLocal, ITXDBVERSION);
            }
            UpdateWPZUORD(dwLocal);
        }
        public void InsertWPZUORD(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO WPZUORD(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryWPZUORD(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM WPZUORD WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM WPZUORD  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "WPZUORD";
            Return = Result;
            return Return;
        }
        public void DeleteWPZUORD(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM WPZUORD   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateWPZUORD(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE WPZUORD SET "
                    +"WPZMANDANT= :WPZMANDANT,"
                    +"WPZKUNDNR= :WPZKUNDNR,"
                    +"WPZWERK= :WPZWERK,"
                    +"WPZABT= :WPZABT,"
                    +"WPZART= :WPZART,"
                    +"WPZVAR= :WPZVAR,"
                    +"WPZDESS= :WPZDESS,"
                    +"WPZBESCHICH= :WPZBESCHICH,"
                    +"WPZZUSTAND= :WPZZUSTAND,"
                    +"WPZLIEFKD= :WPZLIEFKD,"
                    +"WPZLIEFNR= :WPZLIEFNR,"
                    +"WPZPNRKR= :WPZPNRKR,"
                    +"WPZPLFDNR= :WPZPLFDNR,"
                    +"WPZAENDZL= :WPZAENDZL,"
                    +"WPZAENDBEN= :WPZAENDBEN,"
                    +"WPZAENDDAT= :WPZAENDDAT,"
                    +"WPZNEUBEN= :WPZNEUBEN,"
                    +"WPZNEUDAT= :WPZNEUDAT,"
                    +"WPZSTUFE= :WPZSTUFE,"
                    +"WPZGDATINV= :WPZGDATINV,"
                    +"WPZGDATVON= :WPZGDATVON,"
                    +"WPZGDATBIS= :WPZGDATBIS,"
                    +"WPZDATINT= :WPZDATINT,"
                    +"WPZWEITERE= :WPZWEITERE,"
                    +"WPZGRUPPE1= :WPZGRUPPE1,"
                    +"WPZGRUPPE2= :WPZGRUPPE2,"
                    +"WPZGRUPPE3= :WPZGRUPPE3,"
                    +"WPZGRUPPE4= :WPZGRUPPE4,"
                    +"WPZGRUPPE5= :WPZGRUPPE5,"
                    +"WPZGRUPPE6= :WPZGRUPPE6,"
                    +"WPZGRUPPE7= :WPZGRUPPE7,"
                    +"WPZGRUPPE8= :WPZGRUPPE8,"
                    +"WPZGRUPPE9= :WPZGRUPPE9,"
                    +"WPZGRUPPE10= :WPZGRUPPE10,"
                    +"WPZRPREIS1= :WPZRPREIS1,"
                    +"WPZRPREIS2= :WPZRPREIS2,"
                    +"WPZRPREIS3= :WPZRPREIS3,"
                    +"WPZRPREIS4= :WPZRPREIS4,"
                    +"WPZRPREIS5= :WPZRPREIS5,"
                    +"WPZRPREIS6= :WPZRPREIS6,"
                    +"WPZRPREIS7= :WPZRPREIS7,"
                    +"WPZRPREIS8= :WPZRPREIS8,"
                    +"WPZRPREIS9= :WPZRPREIS9,"
                    +"WPZRPREIS10= :WPZRPREIS10,"
                    +"WPZPRPR= :WPZPRPR,"
                    +"WPZTEILQUAL= :WPZTEILQUAL,"
                    +"WPZFILLER= :WPZFILLER,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("WPZMANDANT", OracleType.NVarChar).Value = dwData["WPZMANDANT"].ToString(); 
          or1.Parameters.Add("WPZKUNDNR", OracleType.NVarChar).Value = dwData["WPZKUNDNR"].ToString(); 
          or1.Parameters.Add("WPZWERK", OracleType.NVarChar).Value = dwData["WPZWERK"].ToString(); 
          or1.Parameters.Add("WPZABT", OracleType.NVarChar).Value = dwData["WPZABT"].ToString(); 
          or1.Parameters.Add("WPZART", OracleType.NVarChar).Value = dwData["WPZART"].ToString(); 
          or1.Parameters.Add("WPZVAR", OracleType.NVarChar).Value = dwData["WPZVAR"].ToString(); 
          or1.Parameters.Add("WPZDESS", OracleType.NVarChar).Value = dwData["WPZDESS"].ToString(); 
          or1.Parameters.Add("WPZBESCHICH", OracleType.NVarChar).Value = dwData["WPZBESCHICH"].ToString(); 
          or1.Parameters.Add("WPZZUSTAND", OracleType.NVarChar).Value = dwData["WPZZUSTAND"].ToString(); 
          or1.Parameters.Add("WPZLIEFKD", OracleType.NVarChar).Value = dwData["WPZLIEFKD"].ToString(); 
          or1.Parameters.Add("WPZLIEFNR", OracleType.NVarChar).Value = dwData["WPZLIEFNR"].ToString(); 
          or1.Parameters.Add("WPZPNRKR", OracleType.NVarChar).Value = dwData["WPZPNRKR"].ToString(); 
          or1.Parameters.Add("WPZPLFDNR", OracleType.NVarChar).Value = dwData["WPZPLFDNR"].ToString(); 
          or1.Parameters.Add("WPZAENDZL", OracleType.NVarChar).Value = dwData["WPZAENDZL"].ToString(); 
          or1.Parameters.Add("WPZAENDBEN", OracleType.NVarChar).Value = dwData["WPZAENDBEN"].ToString(); 
          or1.Parameters.Add("WPZAENDDAT", OracleType.NVarChar).Value = dwData["WPZAENDDAT"].ToString(); 
          or1.Parameters.Add("WPZNEUBEN", OracleType.NVarChar).Value = dwData["WPZNEUBEN"].ToString(); 
          or1.Parameters.Add("WPZNEUDAT", OracleType.NVarChar).Value = dwData["WPZNEUDAT"].ToString(); 
          or1.Parameters.Add("WPZSTUFE", OracleType.NVarChar).Value = dwData["WPZSTUFE"].ToString(); 
          or1.Parameters.Add("WPZGDATINV", OracleType.NVarChar).Value = dwData["WPZGDATINV"].ToString(); 
          or1.Parameters.Add("WPZGDATVON", OracleType.NVarChar).Value = dwData["WPZGDATVON"].ToString(); 
          or1.Parameters.Add("WPZGDATBIS", OracleType.NVarChar).Value = dwData["WPZGDATBIS"].ToString(); 
          or1.Parameters.Add("WPZDATINT", OracleType.NVarChar).Value = dwData["WPZDATINT"].ToString(); 
          or1.Parameters.Add("WPZWEITERE", OracleType.NVarChar).Value = dwData["WPZWEITERE"].ToString(); 
          or1.Parameters.Add("WPZGRUPPE1", OracleType.NVarChar).Value = dwData["WPZGRUPPE1"].ToString(); 
          or1.Parameters.Add("WPZGRUPPE2", OracleType.NVarChar).Value = dwData["WPZGRUPPE2"].ToString(); 
          or1.Parameters.Add("WPZGRUPPE3", OracleType.NVarChar).Value = dwData["WPZGRUPPE3"].ToString(); 
          or1.Parameters.Add("WPZGRUPPE4", OracleType.NVarChar).Value = dwData["WPZGRUPPE4"].ToString(); 
          or1.Parameters.Add("WPZGRUPPE5", OracleType.NVarChar).Value = dwData["WPZGRUPPE5"].ToString(); 
          or1.Parameters.Add("WPZGRUPPE6", OracleType.NVarChar).Value = dwData["WPZGRUPPE6"].ToString(); 
          or1.Parameters.Add("WPZGRUPPE7", OracleType.NVarChar).Value = dwData["WPZGRUPPE7"].ToString(); 
          or1.Parameters.Add("WPZGRUPPE8", OracleType.NVarChar).Value = dwData["WPZGRUPPE8"].ToString(); 
          or1.Parameters.Add("WPZGRUPPE9", OracleType.NVarChar).Value = dwData["WPZGRUPPE9"].ToString(); 
          or1.Parameters.Add("WPZGRUPPE10", OracleType.NVarChar).Value = dwData["WPZGRUPPE10"].ToString(); 
          or1.Parameters.Add("WPZRPREIS1", OracleType.NVarChar).Value = dwData["WPZRPREIS1"].ToString(); 
          or1.Parameters.Add("WPZRPREIS2", OracleType.NVarChar).Value = dwData["WPZRPREIS2"].ToString(); 
          or1.Parameters.Add("WPZRPREIS3", OracleType.NVarChar).Value = dwData["WPZRPREIS3"].ToString(); 
          or1.Parameters.Add("WPZRPREIS4", OracleType.NVarChar).Value = dwData["WPZRPREIS4"].ToString(); 
          or1.Parameters.Add("WPZRPREIS5", OracleType.NVarChar).Value = dwData["WPZRPREIS5"].ToString(); 
          or1.Parameters.Add("WPZRPREIS6", OracleType.NVarChar).Value = dwData["WPZRPREIS6"].ToString(); 
          or1.Parameters.Add("WPZRPREIS7", OracleType.NVarChar).Value = dwData["WPZRPREIS7"].ToString(); 
          or1.Parameters.Add("WPZRPREIS8", OracleType.NVarChar).Value = dwData["WPZRPREIS8"].ToString(); 
          or1.Parameters.Add("WPZRPREIS9", OracleType.NVarChar).Value = dwData["WPZRPREIS9"].ToString(); 
          or1.Parameters.Add("WPZRPREIS10", OracleType.NVarChar).Value = dwData["WPZRPREIS10"].ToString(); 
          or1.Parameters.Add("WPZPRPR", OracleType.NVarChar).Value = dwData["WPZPRPR"].ToString(); 
          or1.Parameters.Add("WPZTEILQUAL", OracleType.NVarChar).Value = dwData["WPZTEILQUAL"].ToString(); 
          or1.Parameters.Add("WPZFILLER", OracleType.NVarChar).Value = dwData["WPZFILLER"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
